﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Repositories
{
    public class StudentRepository : BaseRepository
    {
        public StudentRepository(DB.DBContext dbContext) : base(dbContext) { }

        public int Create(Student student)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = @"
                                INSERT INTO Students (Name, Age, Gender, ClassId, CreatedAt)
                                VALUES (@Name, @Age, @Gender, @ClassId, @CreatedAt);
                                SELECT last_insert_rowid();";
                            command.Parameters.AddWithValue("@Name", student.Name);
                            command.Parameters.AddWithValue("@Age", student.Age);
                            command.Parameters.AddWithValue("@Gender", student.Gender != null ? (object)student.Gender : DBNull.Value);
                            command.Parameters.AddWithValue("@ClassId", student.ClassId);
                            command.Parameters.AddWithValue("@CreatedAt", student.CreatedAt);
                            student.Id = Convert.ToInt32(command.ExecuteScalar());
                        }

                        transaction.Commit();
                        LogAction("CREATE", "Students", student.Id.ToString(), $"Name: {student.Name}, Age: {student.Age}, Gender: {student.Gender}, ClassId: {student.ClassId}");
                        return student.Id;
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public Student GetById(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT s.*, c.Name AS ClassName, sc.Name AS SchoolName
                        FROM Students s
                        JOIN Classes c ON s.ClassId = c.Id
                        JOIN Schools sc ON c.SchoolId = sc.Id
                        WHERE s.Id = @Id";
                    command.Parameters.AddWithValue("@Id", id);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Student
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Age = reader.GetInt32(reader.GetOrdinal("Age")),
                                Gender = reader.IsDBNull(reader.GetOrdinal("Gender")) ? null : reader.GetString(reader.GetOrdinal("Gender")),
                                ClassId = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                                Class = new Class
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                    Name = reader.GetString(reader.GetOrdinal("ClassName")),
                                    School = new School
                                    {
                                        Name = reader.GetString(reader.GetOrdinal("SchoolName"))
                                    }
                                }
                            };
                        }
                    }
                }
            }

            return null;
        }

        public List<Student> GetAll()
        {
            var students = new List<Student>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT s.*, c.Name AS ClassName, sc.Name AS SchoolName
                        FROM Students s
                        JOIN Classes c ON s.ClassId = c.Id
                        JOIN Schools sc ON c.SchoolId = sc.Id
                        ORDER BY sc.Name, c.Name, s.Name";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Age = reader.GetInt32(reader.GetOrdinal("Age")),
                                Gender = reader.IsDBNull(reader.GetOrdinal("Gender")) ? null : reader.GetString(reader.GetOrdinal("Gender")),
                                ClassId = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                                Class = new Class
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                    Name = reader.GetString(reader.GetOrdinal("ClassName")),
                                    School = new School
                                    {
                                        Name = reader.GetString(reader.GetOrdinal("SchoolName"))
                                    }
                                }
                            });
                        }
                    }
                }
            }

            return students;
        }

        public List<Student> GetByClassId(int classId)
        {
            var students = new List<Student>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        SELECT s.*, c.Name AS ClassName, sc.Name AS SchoolName
                        FROM Students s
                        JOIN Classes c ON s.ClassId = c.Id
                        JOIN Schools sc ON c.SchoolId = sc.Id
                        WHERE s.ClassId = @ClassId
                        ORDER BY s.Name";
                    command.Parameters.AddWithValue("@ClassId", classId);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Age = reader.GetInt32(reader.GetOrdinal("Age")),
                                Gender = reader.IsDBNull(reader.GetOrdinal("Gender")) ? null : reader.GetString(reader.GetOrdinal("Gender")),
                                ClassId = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt")),
                                Class = new Class
                                {
                                    Id = reader.GetInt32(reader.GetOrdinal("ClassId")),
                                    Name = reader.GetString(reader.GetOrdinal("ClassName")),
                                    School = new School
                                    {
                                        Name = reader.GetString(reader.GetOrdinal("SchoolName"))
                                    }
                                }
                            });
                        }
                    }
                }
            }

            return students;
        }

        public void Update(Student student)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = @"
                                UPDATE Students
                                SET Name = @Name,
                                    Age = @Age,
                                    Gender = @Gender,
                                    ClassId = @ClassId
                                WHERE Id = @Id";
                            command.Parameters.AddWithValue("@Name", student.Name);
                            command.Parameters.AddWithValue("@Age", student.Age);
                            command.Parameters.AddWithValue("@Gender", student.Gender != null ? (object)student.Gender : DBNull.Value);
                            command.Parameters.AddWithValue("@ClassId", student.ClassId);
                            command.Parameters.AddWithValue("@Id", student.Id);
                            command.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        LogAction("UPDATE", "Students", student.Id.ToString(), $"Name: {student.Name}, Age: {student.Age}, Gender: {student.Gender}, ClassId: {student.ClassId}");
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void Delete(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (var command = new SQLiteCommand(connection))
                        {
                            command.CommandText = "DELETE FROM Students WHERE Id = @Id";
                            command.Parameters.AddWithValue("@Id", id);
                            command.ExecuteNonQuery();
                        }

                        transaction.Commit();
                        LogAction("DELETE", "Students", id.ToString());
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}